package dfbz.com.dao;


import dfbz.com.constant.Constance;
import dfbz.com.dao.base.BaseDao;
import dfbz.com.model.User;
import dfbz.com.model.UserFocus;
import dfbz.com.model.UserInfo;
import dfbz.com.utils.JDBCUtil;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class UserDao extends BaseDao {

    QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
    //加入转下划线为驼峰的对象——将数据库中字段对应model中对象的字段
    BasicRowProcessor bp = new BasicRowProcessor(new GenerousBeanProcessor());

    public User findUserByName(String username) {
        try {
            User user = qr.query("select * from user where username = ? limit 1", new BeanHandler<>(User.class, bp), username);
            System.out.println("找到了用户：" + user);
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
            e.getMessage();
        }
        return null;

    }


    public User findUserByEmail(String email) {
        try {
            User user = qr.query("select * from user where email = ? limit 1", new BeanHandler<>(User.class, bp), email);
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }


    /**
     * 查取总用户数量
     *
     * @return
     */
    public Integer findTotalCount() {

        String sql = "select count(*) from user";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                int count = resultSet.getInt(1);
                System.out.println("查到的总数：" + count);
                return count;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            JDBCUtil.close(resultSet, preparedStatement, connection);
        }

        return null;
    }




    public List<Map<String, Object>> findPageUser2(String current, String row, String search) {
        List<Map<String, Object>> list = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select u.id,u.username,f.real_name,f.gender,f.age,f.describe\n" +
                "from user u\n" +
                "left join user_info f\n" +
                "on u.id=f.user_id\n");
        if (null != search) {
            try {
                sb.append("where u.username like ? or f.real_name like ?");
                list = qr.query(sb.toString(), new MapListHandler(), search + "%", search + "%", Integer.parseInt(current), Integer.parseInt(row));
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            sb.append("order by u.id\n" +
                    "limit ?,?");
            try {
                list = qr.query(sb.toString(), new MapListHandler(), Integer.parseInt(current), Integer.parseInt(row));
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }


    public List<UserFocus> findUserFocus(Object userId) {

        String sql = "select *\n" +
                "from user_focus  where user_id = ?";

        try {
            List<UserFocus> query = qr.query(sql, new BeanListHandler<>(UserFocus.class, bp), userId);
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<UserFocus> findUserFocus(Object userId, Integer current) {

        String sql = "select *\n" +
                "from user_focus  where user_id = ? limit ?,?";
        try {
            List<UserFocus> query = qr.query(sql, new BeanListHandler<>(UserFocus.class, bp), userId,(current-1)* Constance.PAGE_ROW_COUNT, Constance.PAGE_ROW_COUNT);
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Map<String, Object>> findPageUser3(Integer userId, Integer current, String search) {
        List<Map<String, Object>> query = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select u.id,u.username,f.age,f.real_name,f.phone,f.gender,f.describe,fo.user_focus_id\n" +
                "from user u\n" +
                "left join user_info f\n" +
                "on u.id = f.user_id\n" +
                "left join user_focus fo\n" +
                "on u.id = fo.user_focus_id and fo.user_id = ? ");
        if (null != search) {
            try {
                sb.append("where u.username like ? or f.real_name like ?");
                sb.append("order by u.id limit ?,?");
                query = qr.query(sb.toString(), new MapListHandler(), userId, search + "%", search + "%", (current - 1) * Constance.PAGE_ROW_COUNT, Constance.PAGE_ROW_COUNT);

            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            try {
                sb.append("order by u.id limit ?,?");
                query = qr.query(sb.toString(), new MapListHandler(), userId, (current - 1) * Constance.PAGE_ROW_COUNT, Constance.PAGE_ROW_COUNT);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return query;

    }

    public long findListTotalCount(String search) {
        String sql;
        Map<String, Object> query;
        if (search == null) {
            try {
                sql = "select count(*) as total from user u left join user_focus f on u.id = f.user_id";
                query = qr.query(sql, new MapHandler());
                return (Long) query.get("total");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            try {
                sql = "select count(*) as total from user u left join user_info f  on u.id = f.user_id \n" +
                        "where u.username like ? or f.real_name like ?";
                query = qr.query(sql, new MapHandler(), search + "%", search + "%");
                return (Long) query.get("total");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }
}
